Go to the documentation of this file.
00001 using System;
00002 using System.Collections.Generic;
00003 using System.Text;
00004 using System.Xml;
00005 using System.Data;
00006 using System.Data.SqlClient;
00007 using System.Transactions;
00008 using RAIS.Common.DynamicMaskManagement;
00009 using RAIS.Common.TableManagement;
00010 using RAIS.Common.MessageManagement;
00011 using RAIS.Common.UserManagement;
00012 using Fields = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Field>;
00013 using Values = System.Collections.Generic.Dictionary<string, System.Collections.Generic.Dictionary<int, string>>;
00014 namespace RAIS.DataAccessLayer
00015 {
00019     public class LoggingSystem
00020     {
00021         string curUser;
00022         List<FieldData> curFields;
00023         Table curTable;
00024         DataRow curRow;
00025         StringBuilder modifiedXML = new StringBuilder();
00026         XmlWriter xw;
00027 
00028         private readonly string multiLookupQuery = "SELECT " +
00029         "    suo1.[User], suo1.Operation, suo1.[Table], " +
00030         "    suo1.[Field Value] [Primary Key], suo1.Date, " +
00031         "    suo2.[Field Name], suo2.[Field Value] " +
00032         "FROM [System - User Operations] suo1 " +
00033         "    left join [System - User Operations] suo2 on suo1.[Primary Key]=suo2.[Primary Key] " +
00034                 "WHERE suo1.[Table]= '{0}' AND suo2.[Table]= '{0}' AND suo1.[Field Value]='{1}' AND  " +
00035         "         suo1.[Field Value]!= suo2.[Field Value] ";
00036 
00037         public LoggingSystem(DynamicMaskData data)
00038         {
00039             curUser = data.CurrentUser.Login;
00040             curFields = data.CurrentRowValues;
00041             curTable = data.DynamicMask.Table;
00042             curRow = data.CurrentRow;
00043         }
00044         public LoggingSystem(string curUser, List<FieldData> curFields, Table curTable, DataRow curRow)
00045         {
00046             this.curUser = curUser;
00047             this.curFields = curFields;
00048             this.curTable = curTable;
00049             this.curRow = curRow;
00050         }
00051 
00052         public LoggingSystem(){}
00053 
00054         public void WriteMultiLookupDelete(String tab, String multipleLookupName, String keyName, String multipleLookupValue, String keyValue)
00055         {
00056             WriteMultiLookup(true, tab, multipleLookupName, keyName, multipleLookupValue, keyValue);
00057         }
00058 
00059         public void WriteMultiLookupInsert(String tab, String multipleLookupName, String keyName, String multipleLookupValue, String keyValue)
00060         {
00061             WriteMultiLookup(false, tab, multipleLookupName, keyName, multipleLookupValue, keyValue);
00062         }
00063 
00064         private void WriteMultiLookup(bool isDelete, String tab, String multipleLookupName, String keyName, String multipleLookupValue, String keyValue)
00065         {
00066             String primaryName = "";
00067             String primaryValue="";
00068             String _keyValue=keyValue;
00069             String _multipleLookupValue=multipleLookupValue;
00070             Message.ModificationType operation = Message.ModificationType.Delete;
00071             if (!isDelete)
00072                 operation = Message.ModificationType.Add;
00073 
00074             using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00075             {
00076                 sqlConnection.Open();
00077                 using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
00078                 {
00079                     sqlCommand.CommandText = string.Format("select * from [{0}] WHERE [{0}].[{1}] = {3} AND [{0}].[{2}] = {4}",
00080                         tab, multipleLookupName, keyName, multipleLookupValue, keyValue);
00081                     SqlDataReader reader = sqlCommand.ExecuteReader();
00082                     if (reader.Read())
00083                     {
00084                                                 primaryName = "PK " + tab + " ID";
00085                         primaryValue = reader["PK " + tab + " ID"].ToString();
00086                     }
00087                 }
00088             }
00089             WriteRow(tab, operation, primaryValue, _keyValue, keyName, primaryName);
00090             WriteRow(tab, operation, primaryValue, _multipleLookupValue, multipleLookupName, primaryName);
00091         }
00092 
00093         public void DeleteRow()
00094         {
00095             String valueName;
00096             foreach (FieldData field in curFields)
00097             {
00098                                 if (field.Field.Type != Field.FieldType.Label)
00099                                 {
00100                                         if (field.Field.Name == curTable.PrimaryKeyFieldName || field.Field.Type == Field.FieldType.MultipleLookup) continue;
00101                                         if (field.Field.Type == Field.FieldType.SingleLookup || field.Field.Type == Field.FieldType.LookupPlusIntegerValue
00102                                                 || field.Field.Type == Field.FieldType.LookupPlusScientificValue)
00103                                                 valueName = field.Field.ForeignKeyFieldName;
00104                                         else
00105                                                 valueName = field.Field.Name;
00106                                         WriteRow(curTable.Name, Message.ModificationType.Delete, curRow[curTable.PrimaryKeyFieldName].ToString()
00107                                                         , curRow[valueName].ToString(), valueName, curTable.PrimaryKeyFieldName);
00108                                 }
00109             }
00110         }
00111 
00112         public void InsertRow()
00113         {
00114             String valueName;
00115             foreach (FieldData field in curFields)
00116             {
00117                                 if (field.Field.Type != Field.FieldType.Label)
00118                                 {
00119                                         if (field.Field.Name == curTable.PrimaryKeyFieldName || field.Field.Type == Field.FieldType.MultipleLookup) continue;
00120                                         if (field.Field.Type == Field.FieldType.SingleLookup || field.Field.Type == Field.FieldType.LookupPlusIntegerValue
00121                                                         || field.Field.Type == Field.FieldType.LookupPlusScientificValue)
00122                                                 valueName = field.Field.ForeignKeyFieldName;
00123                                         else
00124                                                 valueName = field.Field.Name;
00125                                         WriteRow(curTable.Name, Message.ModificationType.Add, curRow[curTable.PrimaryKeyFieldName].ToString()
00126                                                         , curRow[valueName].ToString(), valueName, curTable.PrimaryKeyFieldName);
00127                                 }
00128             }
00129         }
00130 
00131         public void UpdateValue(String oldValue, String newValue, FieldData field)
00132         {
00133             String tabName = curTable.Name;
00134             String value;
00135             value = newValue;
00136             if (oldValue == newValue) return;
00137             WriteRow(tabName, Message.ModificationType.Edit, curRow[curTable.PrimaryKeyFieldName].ToString()
00138                                 , value, field.Field.Name, curTable.PrimaryKeyFieldName);
00139 
00140         }
00141 
00142         public void Open()
00143         {
00144             xw = System.Xml.XmlWriter.Create(modifiedXML);
00145             xw.WriteStartElement("HistoryRows");
00146         }
00147 
00148         public void Close()
00149         {
00150             xw.WriteEndElement();
00151             xw.Close();
00152             modifiedXML.Replace("<?xml version=\"1.0\" encoding=\"utf-8\" ?> ", "");
00153             using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00154             {
00155                 sqlConnection.Open();
00156                 using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
00157                 {
00158                     sqlCommand.CommandType = CommandType.StoredProcedure;
00159                     sqlCommand.CommandText = "usp_AddHistoryUserOperations";
00160                     DataAccessUtilities.AddParameter(sqlCommand, "@xml", modifiedXML.ToString());
00161                     sqlCommand.ExecuteNonQuery();
00162                 }
00163             }
00164         }
00165         
00166         private void WriteRow(String table, Message.ModificationType operation, String primaryKey, String fieldValue, 
00167                                 String fieldName, String primaryName)
00168         { 
00169             xw.WriteStartElement("Row");
00170             xw.WriteAttributeString("User", curUser);            
00171             xw.WriteAttributeString("Operation", ((int)operation).ToString());
00172             xw.WriteAttributeString("Table", table);
00173             xw.WriteAttributeString("PrimaryKey", primaryKey);
00174             xw.WriteAttributeString("FieldValue", fieldValue);
00175             xw.WriteAttributeString("FieldName", fieldName);
00176             xw.WriteAttributeString("PrimaryName", primaryName);
00177             xw.WriteEndElement();
00178         }
00179 
00180 
00181         private Fields FillLookupValues(Table tab, out Values lookupValues1, out Values multiLookupValues1, out List<string> multiLoocupTables1)
00182         {
00183             Fields f = TableManagement.GetFieldsByTable(tab);
00184             Fields fields = new Fields();
00185             Values lookupValues = new Values();
00186             Values multiLookupValues = new Values();
00187             List<string> multiLoocupTables = new List<string>();
00188 
00189             foreach (Field field in f.Values)
00190             {
00191                 
00192                 if (field.Type == Field.FieldType.SingleLookup)
00193                 {
00194                     fields[field.ForeignKeyFieldName] = field;
00195                 }
00196                 else if (field.Type == Field.FieldType.MultipleLookup)
00197                 {
00198                     fields[field.ForeignKeyFieldName] = field;
00199                 }
00200                 else fields[field.Name] = field;
00201             }
00202             lookupValues1 = lookupValues;
00203             multiLookupValues1 = multiLookupValues;
00204             multiLoocupTables1 = multiLoocupTables;
00205             return fields;
00206         }
00207 
00208         private string GetCommandText(int primaryKey, List<string> multiLoocupTables, string tabName)
00209         {
00210             StringBuilder str = new StringBuilder();
00211             str.Append("SELECT [User], [Operation], [Table], [Primary Key], "+
00212                         " [Date], [Field Name], [Field Value] FROM [dbo].[System - User Operations] WHERE [Table]='"+tabName+"' and [Primary Key]=" + primaryKey.ToString());
00213 
00214             foreach (string tabs in multiLoocupTables)
00215                 str.Append (" union " + string.Format(multiLookupQuery, tabs, primaryKey));
00216             str.Append(" order by [Date] ");
00217             return str.ToString();
00218         }
00219 
00220         public Field GetFieldByName(string fieldName, Fields fields)
00221         {
00222             foreach (Field field in fields.Values)
00223                 if (field.Name == fieldName||field.ForeignKeyFieldName == fieldName)
00224                     return field;
00225             return null;
00226         }
00227 
00228         public List<Dictionary<string,object>> LoadHistory(int tableID, int primaryKey)
00229         {
00230             Table tab = TableManagement.GetTableByTableID(tableID);
00231             Fields fields = TableManagement.GetFieldsByTable(tab);            
00232             List<string> multiLoocupTables=new List<string>();
00233 
00234             foreach (Field field in fields.Values)
00235                 if (field.Type == Field.FieldType.MultipleLookup)
00236                     multiLoocupTables.Add(tab.Name + " " + field.RelatedTable.Name);
00237 
00238             List<Dictionary<string, object>> history = new List<Dictionary<string, object>>();
00239             
00240             using (TransactionScope ts = new TransactionScope())
00241             {
00242                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00243                 {
00244                     sqlConnection.Open();
00245                     using (SqlConnection lookupSqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00246                     {
00247                         lookupSqlConnection.Open();
00248                         using (SqlCommand sqlCommand = new SqlCommand(GetCommandText(primaryKey, multiLoocupTables, tab.Name), sqlConnection))
00249                         {
00250                             using (SqlDataReader reader = sqlCommand.ExecuteReader())
00251                             {
00252                                 while (reader.Read())
00253                                 {
00254                                     Dictionary<string, object> dic = new Dictionary<string, object>();
00255                                     dic["Date"] = reader["Date"];
00256                                     dic["User"] = reader["User"];
00257                                     dic["Operation"] = reader["Operation"];
00258 
00259                                     string fieldName = (string)reader["Field Name"];
00260                                     string fieldValue = (string)reader["Field Value"];
00261 
00262 
00263 
00264                                     Field tempField = GetFieldByName(fieldName, fields);
00265                                     if (tempField == null) continue;
00266                                     if (tempField.Type == Field.FieldType.SingleLookup || tempField.Type == Field.FieldType.LookupPlusIntegerValue
00267                                             || tempField.Type == Field.FieldType.LookupPlusScientificValue)
00268                                     {
00269                                         if (!string.IsNullOrEmpty(fieldValue))
00270                                             dic["Field Value"] = GetLookupValue(lookupSqlConnection, tempField, fieldValue);
00271                                         else dic["Field Value"] = "";
00272                                         dic["Field Name"] = tempField.Name;
00273                                     }
00274                                     else if (tempField.Type == Field.FieldType.MultipleLookup)
00275                                     {
00276                                         if (!string.IsNullOrEmpty(fieldValue))
00277                                             dic["Field Value"] = GetLookupValue(lookupSqlConnection, tempField, fieldValue);
00278                                         else dic["Field Value"] = "";
00279                                         dic["Field Name"] = tempField.Name;
00280                                     }
00281                                     else
00282                                     {
00283                                         dic["Field Name"] = fieldName;
00284                                         dic["Field Value"] = fieldValue;
00285                                     }
00286                                     history.Add(dic);
00287                                 }
00288                             }
00289                         }
00290                     }
00291                 }
00292                 ts.Complete();
00293             }
00294             return history;
00295         }
00296 
00297         private string GetLookupValue(SqlConnection sqlConnection, Field field, string fieldValue)
00298         {
00299                         field.RelatedTable.Fields = TableManagement.GetFieldsByTable(field.RelatedTable);
00300 
00301             using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
00302             {
00303                 sqlCommand.CommandText = "select * from [" + field.RelatedTable.Name + "] where [" + field.RelatedTable.PrimaryKeyFieldName + "] = "+fieldValue;
00304                 using (SqlDataReader reader = sqlCommand.ExecuteReader())
00305                 {
00306                     while (reader.Read())
00307                                         {
00308                                                 string returnValue = string.Empty;
00309                                                 if (!string.IsNullOrEmpty(field.RelatedTable.VisibleTextFieldName))
00310                                                         returnValue = reader[field.RelatedTable.VisibleTextFieldName].ToString();
00311                                                 else if (reader.FieldCount > 1)
00312                                                         returnValue = reader[1].ToString();
00313                                                 else if (reader.FieldCount == 1)
00314                                                         returnValue = reader[0].ToString();
00315 
00316                                                 return returnValue;
00317                                         }
00318                 }
00319             }
00320             return "";
00321         }
00322     }
00323 }